﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using E4RPTools;
using MobileLoader.DataSet;

namespace MobileLoader
{
    public class Database
    {
        
        private static void InsertTmnLoad(SqlTransaction sqlTransaction, TMNCarregamentoDataSet.CarregamentoRow load)
        {
            const string query =
                @"  insert 
                    into    TMNCarregamento(SAPLoja, Telemovel, FacturaRecibo, Valor, DataHora, Cheque, Banco) 
                    values  (@SAPLoja, @Telemovel, @FacturaRecibo, @Valor, @DataHora, @Cheque, @Banco)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("SAPLoja", load.SAPLoja),
                                        new SqlParameter("Telemovel", load.Telemovel),
                                        new SqlParameter("FacturaRecibo", load.FacturaRecibo),
                                        new SqlParameter("Valor", load.Valor),
                                        new SqlParameter("DataHora", load.DataHora),                                        
                                        new SqlParameter("Cheque", load.IsChequeNull() ? Convert.DBNull : load.Cheque),
                                        new SqlParameter("Banco", load.IsBancoNull() ? Convert.DBNull : load.Banco)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void InsertTmnBill(SqlTransaction sqlTransaction, TMNFacturaDataSet.FacturaRow bill)
        {
            const string query =
                @"  insert 
                    into    TMNFactura(SAPLoja, Empresa, Factura, Recibo, Conta, Valor, DataHora, Cheque, Banco) 
                    values  (@SAPLoja, @Empresa, @Factura, @Recibo, @Conta, @Valor, @DataHora, @Cheque, @Banco)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("SAPLoja", bill.SAPLoja),
                                        new SqlParameter("Empresa", bill.Empresa),
                                        new SqlParameter("Factura", bill.Factura),
                                        new SqlParameter("Recibo", bill.IsReciboNull() ? Convert.DBNull : bill.Recibo),
                                        new SqlParameter("Conta", bill.IsContaNull() ? Convert.DBNull : bill.Conta),
                                        new SqlParameter("Valor", bill.Valor),
                                        new SqlParameter("DataHora", bill.DataHora),                                        
                                        new SqlParameter("Cheque", bill.IsChequeNull() ? Convert.DBNull : bill.Cheque),
                                        new SqlParameter("Banco", bill.IsBancoNull() ? Convert.DBNull : bill.Banco)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static bool ExistsTmnMovement(SqlTransaction sqlTransaction, int shop, DateTime dateTime)
        {
            const string query =
                @"  select  count(*) 
                    from    TMNMovimento
                    where   SAPLoja = @SAPLoja 
                            and Data = @Data";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("SAPLoja", shop),
                                        new SqlParameter("Data", dateTime.Date)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                return sqlDataAccess.ExecuteScalar(sqlTransaction, 0, query, sqlParameters.ToArray()) == 1;
        }

        private static void InsertTmnMovement(SqlTransaction sqlTransaction, int shop, DateTime dateTime, double load, double bill)
        {
            const string query =
                @"  insert 
                    into    TMNMovimento(SAPLoja, Data, Carregamento, AnulacaoCarregamento, Factura, AnulacaoFactura, Regularizacao, Saldo, Deposito, Cheque, Diferencial, Fechado) 
                    values  (@SAPLoja, @Data, @Carregamento, 0, @Factura, 0, 0, @Saldo, 0, 0, @Diferencial, 0)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("SAPLoja", shop),
                                        new SqlParameter("Data", dateTime.Date),
                                        new SqlParameter("Carregamento", load),
                                        new SqlParameter("Factura", bill),
                                        new SqlParameter("Saldo", load + bill),
                                        new SqlParameter("Diferencial",  - load - bill)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void SaveTmnMovement(SqlTransaction sqlTransaction, int shop, DateTime dateTime, double value, bool isLoad, bool isBill, bool isCheque)
        {
            if(!ExistsTmnMovement(sqlTransaction, shop, dateTime))
            {
                var load = isLoad ? value : 0;
                var bill = isBill ? value : 0;
                InsertTmnMovement(sqlTransaction, shop, dateTime, load, bill);
                return;
            }
            TMNMovimentoDataSet dataSet = GetTmnMovement(sqlTransaction, shop, dateTime);
            if (isLoad) dataSet.Movimento[0].Carregamento += value;
            if (isBill) dataSet.Movimento[0].Factura += value;
            dataSet.Movimento[0].Saldo = dataSet.Movimento[0].Carregamento + dataSet.Movimento[0].Factura -
                                         dataSet.Movimento[0].AnulacaoCarregamento -
                                         dataSet.Movimento[0].AnulacaoFactura + dataSet.Movimento[0].Regularizacao;
            dataSet.Movimento[0].Diferencial = dataSet.Movimento[0].Deposito - dataSet.Movimento[0].Saldo;
            dataSet.Movimento[0].Cheque += isCheque ? value : 0; 
            UpdateTmnMovement(sqlTransaction, dataSet);
        }

        private static void ResetTmnLoadMovement(SqlTransaction sqlTransaction, DateTime dateTime)
        {
            const string query =
                @"  update  TMNMovimento
                    set     Carregamento = 0,
                            Saldo = Factura - AnulacaoCarregamento - AnulacaoFactura + Regularizacao,
                            Diferencial = Deposito - (Factura - AnulacaoCarregamento - AnulacaoFactura + Regularizacao)
                    where   Data = @Data";
            const string chequeQuery =
                @"  update	TMNMovimento
                    set		Cheque =  (
			                    select	case when SUM(valor) IS null then 0 else SUM(valor) end
			                    from	TMNFactura f
			                    where	YEAR(DataHora) = @Ano 
					                    and MONTH(DataHora) = @Mes
					                    and DAY(DataHora) = @Dia
					                    and Cheque != ''
					                    and TMNMovimento.SAPLoja = f.SAPLoja)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Ano", dateTime.Year),
                                        new SqlParameter("Mes", dateTime.Month),
                                        new SqlParameter("Dia", dateTime.Day)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("Data", dateTime.Date));
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, chequeQuery, sqlParameters.ToArray());
            }
        }

        private static void ResetTmnBillMovement(SqlTransaction sqlTransaction, DateTime dateTime)
        {
            const string query =
                @"  update  TMNMovimento
                    set     Factura = 0,
                            Saldo = Carregamento - AnulacaoCarregamento - AnulacaoFactura + Regularizacao,
                            Diferencial = Deposito - (Carregamento - AnulacaoCarregamento - AnulacaoFactura + Regularizacao)
                    where   Data = @Data";
            const string chequeQuery =
                @"  update	TMNMovimento
                    set		Cheque =  (
			                    select	case when SUM(valor) IS null then 0 else SUM(valor) end
			                    from	TMNCarregamento c
			                    where	YEAR(DataHora) = @Ano 
					                    and MONTH(DataHora) = @Mes
					                    and DAY(DataHora) = @Dia
					                    and Cheque != ''
					                    and TMNMovimento.SAPLoja = c.SAPLoja)";

            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Ano", dateTime.Year),
                                        new SqlParameter("Mes", dateTime.Month),
                                        new SqlParameter("Dia", dateTime.Day)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("Data", dateTime.Date));
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, chequeQuery, sqlParameters.ToArray());
            }
        }


        public static TMNCarregamentoDataSet GetTmnLoads(SqlTransaction sqlTransaction, ListFilter listFilter)
        {
            var dataSet = new TMNCarregamentoDataSet();
            const string query =
                @"  select  SAPLoja, Telemovel, FacturaRecibo, Valor, DataHora, Cheque, Banco
                    from    TMNCarregamento
                    {0}";
            var filter = "";
            var parameters = new List<SqlParameter>();
            if (listFilter.Shop != -1)
            {
                filter += " SAPLoja = @SAPLoja";
                parameters.Add(new SqlParameter("SAPLoja", listFilter.Shop));
            }

            if (listFilter.PeriodBegin != DateTime.MinValue)
            {
                if (filter.Length > 0) filter += " and ";
                filter += " dateAdd(dd,datediff(dd,0,DataHora),0) >= @DataInicio";
                parameters.Add(new SqlParameter("DataInicio", listFilter.PeriodBegin.Date));
            }

            if (listFilter.PeriodEnd != DateTime.MinValue)
            {
                if (filter.Length > 0) filter += " and ";
                filter += " dateAdd(dd,datediff(dd,0,DataHora),0) <= @DataFim";
                parameters.Add(new SqlParameter("DataFim", listFilter.PeriodEnd.Date.AddDays(1)));
            }
            if (filter != "") filter = " where " + filter;
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill(sqlTransaction, dataSet.Carregamento, string.Format(query, filter), parameters.ToArray());
            return dataSet;
        }

        public static void InsertTmnLoads(SqlTransaction sqlTransaction, TMNCarregamentoDataSet dataSet)
        {
            foreach(TMNCarregamentoDataSet.CarregamentoRow load in dataSet.Carregamento)
            {
                InsertTmnLoad(sqlTransaction, load);
                SaveTmnMovement(sqlTransaction, load.SAPLoja, load.DataHora, load.Valor, true, false, !load.IsChequeNull() && load.Cheque != "");
            }
        }

        public static void DeleteTmnLoads(SqlTransaction sqlTransaction, DateTime dateTime)
        {
            const string query =
                @"  delete  TMNCarregamento
                    where   YEAR(DataHora) = @Ano 
                            and MONTH(DataHora) = @Mes
                            and DAY(DataHora) = @Dia";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Ano", dateTime.Year),
                                        new SqlParameter("Mes", dateTime.Month),
                                        new SqlParameter("Dia", dateTime.Day)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
            ResetTmnLoadMovement(sqlTransaction, dateTime);
        }

        public static bool ExistsTmnLoads(SqlTransaction sqlTransaction, DateTime dateTime)
        {
            const string query =
                @"  select  count(*) 
                    from    TMNCarregamento
                    where   YEAR(DataHora) = @Ano 
                            and MONTH(DataHora) = @Mes
                            and DAY(DataHora) = @Dia";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Ano", dateTime.Year),
                                        new SqlParameter("Mes", dateTime.Month),
                                        new SqlParameter("Dia", dateTime.Day)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                return sqlDataAccess.ExecuteScalar(sqlTransaction, 0, query, sqlParameters.ToArray()) > 0;
        }

        public static TMNFacturaDataSet GetTmnBills(SqlTransaction sqlTransaction, ListFilter listFilter)
        {
            var dataSet = new TMNFacturaDataSet();
            const string query =
                @"  select  SAPLoja, Empresa, Factura, Recibo, Conta, Valor, DataHora, Cheque, Banco
                    from    TMNFactura
                    {0}";
            var filter = "";
            var parameters = new List<SqlParameter>();
            if (listFilter.Shop != -1)
            {
                filter += " SAPLoja = @SAPLoja";
                parameters.Add(new SqlParameter("SAPLoja", listFilter.Shop));
            }

            if (listFilter.PeriodBegin != DateTime.MinValue)
            {
                if (filter.Length > 0) filter += " and ";
                filter += " dateAdd(dd,datediff(dd,0,DataHora),0) >= @DataInicio";
                parameters.Add(new SqlParameter("DataInicio", listFilter.PeriodBegin.Date));
            }

            if (listFilter.PeriodEnd != DateTime.MinValue)
            {
                if (filter.Length > 0) filter += " and ";
                filter += " dateAdd(dd,datediff(dd,0,DataHora),0) <= @DataFim";
                parameters.Add(new SqlParameter("DataFim", listFilter.PeriodEnd.Date));
            }
            if (filter != "") filter = " where " + filter;
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill(sqlTransaction, dataSet.Factura, string.Format(query, filter), parameters.ToArray());
            return dataSet;
        }

        public static void InsertTmnBills(SqlTransaction sqlTransaction, TMNFacturaDataSet dataSet)
        {
            foreach (TMNFacturaDataSet.FacturaRow bill in dataSet.Factura)
            {
                InsertTmnBill(sqlTransaction, bill);
                SaveTmnMovement(sqlTransaction, bill.SAPLoja, bill.DataHora, bill.Valor, false, true, !bill.IsChequeNull() && bill.Cheque != "");
            }
        }

        public static void DeleteTmnBills(SqlTransaction sqlTransaction, DateTime dateTime)
        {
            const string query =
                @"  delete  TMNFactura
                    where   YEAR(DataHora) = @Ano 
                            and MONTH(DataHora) = @Mes
                            and DAY(DataHora) = @Dia";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Ano", dateTime.Year),
                                        new SqlParameter("Mes", dateTime.Month),
                                        new SqlParameter("Dia", dateTime.Day)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
            ResetTmnBillMovement(sqlTransaction, dateTime);
        }

        public static bool ExistsTmnBills(SqlTransaction sqlTransaction, DateTime dateTime)
        {
            const string query =
                @"  select  count(*) 
                    from    TMNFactura
                    where   YEAR(DataHora) = @Ano 
                            and MONTH(DataHora) = @Mes
                            and DAY(DataHora) = @Dia";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Ano", dateTime.Year),
                                        new SqlParameter("Mes", dateTime.Month),
                                        new SqlParameter("Dia", dateTime.Day)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                return sqlDataAccess.ExecuteScalar(sqlTransaction, 0, query, sqlParameters.ToArray()) > 0;
        }

        public static TMNMovimentoDataSet GetTmnMovements(SqlTransaction sqlTransaction, ListFilter listFilter)
        {
            var dataSet = new TMNMovimentoDataSet();
            const string query =
                @"  select  SAPLoja, Data, Carregamento, AnulacaoCarregamento, Factura, AnulacaoFactura, Regularizacao, Saldo, Deposito, Diferencial, Observacoes, Fechado, Cheque
                    from    TMNMovimento
                    {0}";
            var filter = "";
            var parameters = new List<SqlParameter>();
            if(listFilter.Shop != -1)
            {
                filter += " SAPLoja = @SAPLoja";
                parameters.Add(new SqlParameter("SAPLoja", listFilter.Shop));
            }

            if (listFilter.PeriodBegin != DateTime.MinValue)
            {
                if (filter.Length > 0) filter += " and ";
                filter += " dateAdd(dd,datediff(dd,0,Data),0) >= @DataInicio";
                parameters.Add(new SqlParameter("DataInicio", listFilter.PeriodBegin.Date));
            }

            if (listFilter.PeriodEnd != DateTime.MinValue)
            {
                if (filter.Length > 0) filter += " and ";
                filter += " dateAdd(dd,datediff(dd,0,Data),0) <= @DataFim";
                parameters.Add(new SqlParameter("DataFim", listFilter.PeriodEnd.Date.AddDays(1)));
            }
            if (filter != "") filter = " where " + filter;
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill(sqlTransaction, dataSet.Movimento, string.Format(query, filter), parameters.ToArray());
            return dataSet;
        }

        public static TMNMovimentoDataSet GetTmnMovement(SqlTransaction sqlTransaction, int shop, DateTime dateTime)
        {
            var dataSet = new TMNMovimentoDataSet();
            const string query =
                @"  select  SAPLoja, Data, Carregamento, AnulacaoCarregamento, Factura, AnulacaoFactura, Regularizacao, Saldo, Deposito, Diferencial, Observacoes, Fechado, Cheque
                    from    TMNMovimento
                    where   SAPLoja = @SAPLoja 
                            and Data = @Data";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill(sqlTransaction, dataSet.Movimento, query, new SqlParameter("SAPLoja", shop), new SqlParameter("Data", dateTime.Date));
            return dataSet;
        }

        public static void UpdateTmnMovement(SqlTransaction sqlTransaction, TMNMovimentoDataSet dataSet)
        {
            const string query =
                @"  update  TMNMovimento
                    set     Carregamento = @Carregamento,
                            AnulacaoCarregamento = @AnulacaoCarregamento,
                            Factura = @Factura,
                            AnulacaoFactura = @AnulacaoFactura,
                            Regularizacao = @Regularizacao, 
                            Saldo = @Saldo,
                            Deposito = @Deposito,
                            Cheque = @Cheque,
                            Diferencial = @Diferencial,
                            Observacoes = @Observacoes,
                            Fechado = @Fechado
                    where   SAPLoja = @SAPLoja 
                            and Data = @Data";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("SAPLoja", dataSet.Movimento[0].SAPLoja),
                                        new SqlParameter("Data", dataSet.Movimento[0].Data),
                                        new SqlParameter("Carregamento", dataSet.Movimento[0].Carregamento),
                                        new SqlParameter("AnulacaoCarregamento", dataSet.Movimento[0].AnulacaoCarregamento),
                                        new SqlParameter("Factura", dataSet.Movimento[0].Factura),
                                        new SqlParameter("AnulacaoFactura", dataSet.Movimento[0].AnulacaoFactura),
                                        new SqlParameter("Regularizacao", dataSet.Movimento[0].Regularizacao),                                        
                                        new SqlParameter("Saldo", dataSet.Movimento[0].Saldo),
                                        new SqlParameter("Deposito", dataSet.Movimento[0].Deposito),
                                        new SqlParameter("Cheque", dataSet.Movimento[0].Cheque),
                                        new SqlParameter("Diferencial", dataSet.Movimento[0].Diferencial),
                                        new SqlParameter("Observacoes", dataSet.Movimento[0].IsObservacoesNull() ? Convert.DBNull : dataSet.Movimento[0].Observacoes),
                                        new SqlParameter("Fechado", dataSet.Movimento[0].Fechado)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        public static TMNLojasDataSet GetShopsOfTmnMovements(SqlTransaction sqlTransaction)
        {
            var dataSet = new TMNLojasDataSet();
            dataSet.Loja.AddLojaRow(-1, "");
            const string query =
                @"  select  distinct SAP, Nome
                    from    TMNMovimento, TMNLoja
                    where   SAP = SAPLoja
                    order by Nome";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill(sqlTransaction, dataSet.Loja, query);
            return dataSet;
        }

        public static TMNLojasDataSet GetShopsOfTmnBills(SqlTransaction sqlTransaction)
        {
            var dataSet = new TMNLojasDataSet();
            dataSet.Loja.AddLojaRow(-1, "");
            const string query =
                @"  select  distinct SAP, Nome
                    from    TMNFactura, TMNLoja
                    where   SAP = SAPLoja
                    order by Nome";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill(sqlTransaction, dataSet.Loja, query);
            return dataSet;
        }

        public static TMNLojasDataSet GetShopsOfTmnLoads(SqlTransaction sqlTransaction)
        {
            var dataSet = new TMNLojasDataSet();
            dataSet.Loja.AddLojaRow(-1, "");
            const string query =
                @"  select  distinct SAP, Nome
                    from    TMNCarregamento, TMNLoja
                    where   SAP = SAPLoja
                    order by Nome";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill(sqlTransaction, dataSet.Loja, query);
            return dataSet;
        }

        public static TMNCarregamentoFacturaDataSet GetTmnDocuments(SqlTransaction sqlTransaction, ListFilter listFilter)
        {
            var dataSet = new TMNCarregamentoFacturaDataSet();
            const string query =
                @"  select  'Carregamento' as Tipo, 
                            SAPLoja, Valor, DataHora, Cheque, Banco, 
                            '' as Empresa, '' as Factura, '' as Recibo, '' as Conta, 
                            Telemovel, FacturaRecibo
                    from    TMNCarregamento
                    {0}
                    union
                    select  'Factura' as Tipo, 
                            SAPLoja, Valor, DataHora, Cheque, Banco, 
                            Empresa, Factura, Recibo, Conta, 
                            '' as Telemovel, '' as FacturaRecibo
                    from    TMNFactura
                    {0}";
            var filter = "";
            var parameters = new List<SqlParameter>();
            if (listFilter.Shop != -1)
            {
                filter += " SAPLoja = @SAPLoja";
                parameters.Add(new SqlParameter("SAPLoja", listFilter.Shop));
            }

            if (listFilter.PeriodBegin != DateTime.MinValue)
            {
                if (filter.Length > 0) filter += " and ";
                filter += " dateAdd(dd,datediff(dd,0,DataHora),0) >= @DataInicio";
                parameters.Add(new SqlParameter("DataInicio", listFilter.PeriodBegin.Date));
            }

            if (listFilter.PeriodEnd != DateTime.MinValue)
            {
                if (filter.Length > 0) filter += " and ";
                filter += " dateAdd(dd,datediff(dd,0,DataHora),0) <= @DataFim";
                parameters.Add(new SqlParameter("DataFim", listFilter.PeriodEnd.Date.AddDays(1)));
            }
            if (filter != "") filter = " where " + filter;
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill(sqlTransaction, dataSet.Documento, string.Format(query, filter), parameters.ToArray());
            return dataSet;
        }

        public static TMNLojasDataSet GetShopsOfTmnLoadsAndBills(SqlTransaction sqlTransaction)
        {
            var dataSet = new TMNLojasDataSet();
            dataSet.Loja.AddLojaRow(-1, "");
            const string query =
                @"  select  distinct SAP, Nome
                    from    TMNFactura, TMNLoja
                    where   SAP = SAPLoja
                    union all
                    select  distinct SAP, Nome
                    from    TMNCarregamento, TMNLoja
                    where   SAP = SAPLoja
                    order by Nome";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill(sqlTransaction, dataSet.Loja, query);
            return dataSet;
        }

        public static TMNLojasDataSet GetShops(SqlTransaction sqlTransaction)
        {
            var dataSet = new TMNLojasDataSet();
            dataSet.Loja.AddLojaRow(-1, "");
            const string query =
                @"  select  SAP, Nome
                    from    TMNLoja";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill(sqlTransaction, dataSet.Loja, query);
            return dataSet;
        }


        public static TMNImpressaoDataSet GetPrintTmnList(SqlTransaction sqlTransaction, DateTime dateTime, int shop)
        {
            var dataSet = new TMNImpressaoDataSet();
            const string shopQuery =
                @"  select  distinct SAP, Nome
                    from    TMNFactura, TMNLoja
                    where   SAP = SAPLoja
                            and dateAdd(dd,datediff(dd,0,DataHora),0) = @Data    
                            and (SAPLoja = @SAPLoja or @SAPLoja = -1)
                    union
                    select  distinct SAP, Nome
                    from    TMNCarregamento, TMNLoja
                    where   SAP = SAPLoja
                            and dateAdd(dd,datediff(dd,0,DataHora),0) = @Data    
                            and (SAPLoja = @SAPLoja or @SAPLoja = -1)
                    order by Nome";
            const string loadsquery =
                @"  select  SAPLoja, Telemovel, FacturaRecibo, Valor, DataHora, Cheque, Banco, 
                            case Cheque when '' then 0 else Valor end as ValorCheque,
                            case Cheque when '' then Valor else 0 end as ValorNumerario
                    from    TMNCarregamento
                    where   dateAdd(dd,datediff(dd,0,DataHora),0) = @Data    
                            and (SAPLoja = @SAPLoja or @SAPLoja = -1)";
            const string billsQuery =
                @"  select  SAPLoja, Valor, DataHora, Cheque, Banco, 
                            Empresa, Factura, Recibo, Conta, 
                            case Cheque when '' then 0 else Valor end as ValorCheque,
                            case Cheque when '' then Valor else 0 end as ValorNumerario
                    from    TMNFactura
                    where   dateAdd(dd,datediff(dd,0,DataHora),0) = @Data
                            and (SAPLoja = @SAPLoja or @SAPLoja = -1)";
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.Fill(sqlTransaction, dataSet.Loja, shopQuery, new SqlParameter("Data", dateTime.Date), new SqlParameter("SAPLoja", shop));
                sqlDataAccess.Fill(sqlTransaction, dataSet.Carregamento, loadsquery, new SqlParameter("Data", dateTime.Date), new SqlParameter("SAPLoja", shop));
                sqlDataAccess.Fill(sqlTransaction, dataSet.Factura, billsQuery, new SqlParameter("Data", dateTime.Date), new SqlParameter("SAPLoja", shop));
            }
            return dataSet;
        }
    }
}
